﻿CREATE TRIGGER [dbo].[trs_Bankruptcy]
ON [dbo].[t_Bankruptcy]
FOR INSERT,UPDATE,DELETE
AS

IF @@ROWCOUNT = 0 RETURN

DECLARE @LoanID int
SELECT @LoanID = ID FROM inserted WHERE StatusID = 0
IF NOT @LoanID IS NULL BEGIN
	ROLLBACK
	RAISERROR('Bankruptcy:%d can not have StatusID set to Zero',16,1,@LoanID)
	RETURN
END

IF [dbo].[Context_IsWorkflowOff]() > 0 RETURN

IF UPDATE(StatusID)
	UPDATE t_Bankruptcy SET StatusDate = GETDATE(),StatusUser = [dbo].[UserName]()
	FROM t_Bankruptcy RC WITH(nolock) INNER JOIN inserted i ON RC.ID = i.ID

--IF UPDATE(StatusDate) AND UPDATE(StatusUser) AND EXISTS(SELECT * FROM deleted) RETURN
IF 'StatusDate,StatusUser' = dbo.fn_ColumnsUpdated(COLUMNS_UPDATED(),'t_Bankruptcy') RETURN

DECLARE @AccessID int,@RowCount int,@StatusID int
CREATE TABLE #StatusIDs(StatusID int NOT NULL)
IF NOT EXISTS(SELECT * FROM inserted)BEGIN
	INSERT INTO #StatusIDs SELECT StatusID FROM deleted
	SELECT @RowCount = COUNT(*),@AccessID = 4 -- Delete
END
ELSE IF NOT EXISTS(SELECT * FROM deleted ) BEGIN
	INSERT INTO #StatusIDs SELECT StatusID FROM inserted
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 8 -- Add
END
ELSE BEGIN
	INSERT INTO #StatusIDs SELECT StatusID FROM deleted
	SELECT @RowCount = @@ROWCOUNT,@AccessID = 2 -- Update
END

SELECT @StatusID = StatusID FROM (SELECT StatusID FROM inserted UNION SELECT StatusID FROM deleted)T

/*T*/IF dbo.Config_Trace() = 1 BEGIN SELECT * INTO #inserted FROM inserted DECLARE @CU varbinary(999) SET @CU=COLUMNS_UPDATED()EXEC sp__TraceTrigger @@PROCID,@CU END/*T*/

DECLARE @CUs varbinary(1024) SET @CUs = COLUMNS_UPDATED()

EXEC sec__CheckTableAccess @RowCount,@CUs,@@PROCID,@StatusID,@AccessID

IF @@ERROR <> 0 RETURN

IF NOT UPDATE (StatusID) AND EXISTS(SELECT * FROM deleted) RETURN

INSERT INTO tlog_State(TablePK,TableName,StateFrom,StateTo)
SELECT i.ID,'t_Bankruptcy',ISNULL(d.StatusID,0),i.StatusID
FROM inserted i LEFT OUTER JOIN deleted d on i.ID = d.ID
WHERE i.StatusID <> ISNULL(d.StatusID,0)

IF @@ROWCOUNT = 0
INSERT INTO tlog_State(TablePK,TableName,StateFrom,StateTo)
SELECT ID,'t_Bankruptcy',0,StatusID FROM inserted
